from datetime import datetime

from sqlalchemy import Column, String, Integer, create_engine, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

# 配置数据库连接

# 创建对象的基类:
Base = declarative_base()


class Person(Base):
    # 表名
    __tablename__ = 'tb_article'
    # 表结构
    id = Column(String(45), primary_key=True)
    title = Column(String(45))
    category_id = Column(String(45))
    create_time = Column(DateTime)

    def __init__(self, id, title, category_id,create_time):
        self.id = id
        self.title = title
        self.category_id = category_id
        self.create_time = create_time


# 初始化数据库连接,:
engine = create_engine('mysql+pymysql://root:123456@192.168.31.134:3308/sob', echo=False)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
session1 = DBSession()

query = session1.query(Person).filter_by(category_id='752171891657015296').order_by(Person.create_time)
results = query.all()  # 或者使用 query.first() 来获取第一个结果

# 遍历查询结果




# session = DBSession()
# # 增操作,增加3条记录
# item1 = Person(id=1, name='xgx', sex='male')
# session.add(item1)
# item2 = Person(id=2, name='xgx1', sex='female')
# session.add(item2)
# item3 = Person(id=3, name='xgx1', sex='male')
# session.add(item3)
# item4 = Person(id=4, name='xgx2', sex='female')
# session.add(item4)
# session.commit()
# session.close()
# 查操作
# session1 = DBSession()
# persons = session1.query(Person).filter(Person.title == '小郭的多彩人生').all()
# for i in range(len(persons)):
#     print(persons[i].id)
#     print(persons[i].title)
#     print(persons[i].user_id)
# session1.close()
# 改操作，将id为2的记录的name字段修改为xxx
# session2 = DBSession()
# session2.query(Person).filter(Person.id == '2').update({Person.name: 'xxx'}, synchronize_session=False)
# session2.commit()
# session2.close()
# # 查看修改结果
# session3 = DBSession()
# print('\n')
# print(session3.query(Person).filter(Person.id == '2').one().name)
# session3.close()
# # 删操作，删除id为3的记录
# session4 = DBSession()
# session4.query(Person).filter(Person.id == '3').delete()
# session4.commit()
# session4.close()
